﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class CommentDAL : ICommentDAL
    {
        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];
        private const string SQL_SELECT_COMMENT1 = "SELECT * FROM Comment WHERE CM_photoId = @photoId";
        private const string SQL_SELECT_COMMENT2 = "INSERT INTO Comment VALUES(@userIdFrom, @userIdTo, @photoId, @text, @time)";
        private const string SQL_SELECT_COMMENT3 = "UPDATE Comment SET CM_userIdFrom=@userIdFrom,CM_userIdTo=@userIdTo,CM_photoId=@photoId,CM_text=@text,CM_time=@time WHERE CM_commentId = @commentId";
        private const string SQL_SELECT_COMMENT4 = "DELETE  FROM Comment WHERE CM_commentId = @commentId";
        private const string SQL_SELECT_COMMENT5 = "DELETE  FROM Comment WHERE CM_photoId = @photoId";
        private const string SQL_SELECT_COMMENT6 = "SELECT * FROM Comment WHERE CM_commentId = @commentId";
        private const string SQL_SELECT_COMMENT7 = "SELECT * FROM Comment WHERE CM_userId = @userId";
        /// <summary>
        /// 根据传入的photoID判断是否存在评论
        /// </summary> 
        public bool commentExist(int photoId)
        {
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_COMMENT1, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }
        /// <summary>
        /// 添加一条评论
        /// </summary>
        public bool addComment(Model.Comment comment)
        {
            bool opetation = false;
            Model.Comment comments = comment;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                // SqlParameter[] paras;
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@userIdFrom", comments.getCM_userIdFrom()), new SqlParameter("@userIdTo", comments.getCM_userIdTo()), new SqlParameter("@photoId", comments.getCM_photoId()), new SqlParameter("@text", comments.getCM_text()), new SqlParameter("@time", comments.getCM_time()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_SELECT_COMMENT2, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }
        /// <summary>
        /// 更新一条评论
        /// </summary>
        public bool updateComment(Model.Comment comment)
        {
            bool opetation = false;
            Model.Comment comments = comment;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                // SqlParameter[] paras;
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@userIdFrom", comments.getCM_userIdFrom()), new SqlParameter("@userIdTo", comments.getCM_userIdTo()), new SqlParameter("@photoId", comments.getCM_photoId()), new SqlParameter("@text", comments.getCM_text()), new SqlParameter("@time", comments.getCM_time()), new SqlParameter("@commentId", comments.getCM_commentId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_SELECT_COMMENT3, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }
        /// <summary>
        /// 删除一条评论
        /// </summary>
        public bool deleteCommentById(int commentId)
        {
            bool opetation = false;
            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@commentId", commentId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_SELECT_COMMENT4, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }
        /// <summary>
        /// 根据photoId删除所有评论
        /// </summary>
        public bool deleteAllByPhotoId(int photoId)
        {
            bool opetation = false;
            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_SELECT_COMMENT5, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }
        /// <summary>
        /// 获取comment对象
        /// </summary>
        public Model.Comment getModel(int commentId)
        {
            Model.Comment comment = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@commentId", commentId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_COMMENT6, para))
                {
                    if (rdr.Read())

                        comment = new Model.Comment(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToInt32(rdr.GetInt32(3)), rdr.GetString(4), Convert.ToDateTime(rdr.GetDateTime(5)));
                    else
                        comment = new Model.Comment();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return comment;

        }
        /// <summary>
        /// 根据userId获取所有评论对象
        /// </summary>
        public List<Model.Comment> getModelByUserId(int userId)
        {
            List<Model.Comment> list = new List<Model.Comment>();
            Model.Comment comment = null;
            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_COMMENT7, para);

                while (rdr.Read())
                {
                    comment = new Model.Comment(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToInt32(rdr.GetInt32(3)), rdr.GetString(4), Convert.ToDateTime(rdr.GetDateTime(5)));

                    list.Add(comment);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }
        /// <summary>
        /// 根据photoId获取所有评论对象
        /// </summary>
        public List<Model.Comment> getModelByPhotoId(int photoId)
        {
            List<Model.Comment> list = new List<Model.Comment>();
            Model.Comment comment = null;
            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@photoId", photoId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_COMMENT1, para);

                while (rdr.Read())
                {
                    comment = new Model.Comment(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToInt32(rdr.GetInt32(3)), rdr.GetString(4), Convert.ToDateTime(rdr.GetDateTime(5)));

                    list.Add(comment);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;

        }

    }
}
